#include <iostream>
#include <iomanip>
#include <vector>
#include <string.h>
#include <sstream>
#include <Windows.h>
#undef UNICODE
#include <sql.h>
#include <sqlext.h>
#include <odbcss.h> //Microsoft specific header file for using ODBC
using namespace std;

void setup(SQLHENV&, SQLRETURN&, SQLHDBC&, SQLHSTMT&); //prepares through setting the database to company
void allocate_handle(SQLRETURN&, SQLHDBC&, SQLHSTMT&);
void deallocate_handle(SQLRETURN&, SQLHDBC&, SQLHSTMT&);
void option_a(SQLRETURN&, SQLHDBC&, SQLHSTMT&);
void option_b(SQLRETURN&, SQLHDBC&, SQLHSTMT&);
void option_c(SQLRETURN&, SQLHDBC&, SQLHSTMT&);
void traverse(int sn, SQLRETURN&, SQLHDBC&, SQLHSTMT&);

inline void gotoxy(int x, int y)
{
	COORD coord;
	coord.X = x; 
	coord.Y = y;
	SetConsoleCursorPosition(GetStdHandle(STD_OUTPUT_HANDLE),
							coord);
}
inline void changeColor(int color)
{
	SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), color);
}

int main ()
{
	ShowCursor(false);
	SQLHENV henv1; //SQL environment handle
	SQLRETURN retvalue;
	SQLHDBC hdbc1;
	SQLHSTMT hstmt1;

	setup(henv1, retvalue, hdbc1, hstmt1);
	

	while(true)
	{
		system("cls");
		allocate_handle(retvalue, hdbc1, hstmt1);
		int choice = 0, selectColor=240, oldchoice=3;
		gotoxy(30,0);
		cout << "Would you like to:";
		while(!GetAsyncKeyState(VK_RETURN))
		{
			if(choice!=oldchoice)
			{
				if(choice==0)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
				gotoxy(5, 3);
				cout << "Create a Presentation";
				if(choice==0)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
				if(choice==1)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
				gotoxy(30, 3);
				cout << "View a Presentation";
				if(choice==1)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
				if(choice==2)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), selectColor);
				gotoxy(60, 3);
				cout << "Exit";
				if(choice==2)
					SetConsoleTextAttribute(GetStdHandle(STD_OUTPUT_HANDLE), 7);
				while(GetAsyncKeyState(VK_LEFT) || GetAsyncKeyState(VK_RIGHT))
				;
			}
			oldchoice=choice;
			if(GetAsyncKeyState(VK_RIGHT))
				choice=(oldchoice+1)%3;
			if(GetAsyncKeyState(VK_LEFT))
			{
				choice = (oldchoice-1);
				if(choice<0)
					choice = 2;
				else 
					choice%=3;
			}
		}

		if(choice==0)
			option_a(retvalue, hdbc1, hstmt1);

		if(choice==1)
			option_b(retvalue, hdbc1, hstmt1);
		

		deallocate_handle(retvalue, hdbc1, hstmt1);
		if(choice==2)
			return 0;
	}
	
}



void setup(SQLHENV& henv1, SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1)
{

	retvalue = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv1);

	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to allocate environment handle" << endl;
		exit(1);
	}

	retvalue = SQLSetEnvAttr(henv1, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);

	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to set environment attribute" << endl;
		exit(1);
	}

	

	retvalue = SQLAllocHandle(SQL_HANDLE_DBC, henv1, &hdbc1);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to allocate database connection handle" << endl;
		exit(1);
	}

	retvalue = SQLConnect(hdbc1, (SQLCHAR*)"COMP244_ODBC", SQL_NTS, NULL, 0, NULL, 0);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to connect to the data source" << endl;
		exit(1);
	}

	
	retvalue = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to allocate statement handle" << endl;
		exit(1);
	}

	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)"Use song_database", SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"Use song_database\"" << endl;
		exit(1);
	}

	retvalue = SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to free hsmt1" << endl;
		exit(1);
	}


}

void allocate_handle(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1)
{

	retvalue = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to allocate statement handle" << endl;
		exit(1);
	}

}

void deallocate_handle(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1)
{
	retvalue = SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to free hsmt1" << endl;
		exit(1);
	}

}

void option_a(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1)
{
	cout << "\nPlease enter the last name of an employee. ";
	bool number, t1, t2, redo=false;
	string name;
	do
	{
		if(redo)
			cout << "\nI'm sorry, that was not a valid entry. Please try again.\nPlease enter the last name of an employee. ";
		number=false;
		t1=false;
		t2=false;
		char temp[]="              ";
		temp[14]=' ';
		cin >> temp;
		cin.sync();
		for(int i=0; i<15; ++i)
		{
			if(isdigit(temp[i]))
			{
				number=true;
				break;
			}
			else if(temp[i]=='-' && t1==false)
				t1=true;
			else if(temp[i]=='-' && t1==true)
			{
				t2=true;
				break;
			}
			else if(t1=true)
				t1=false;
		}
		redo=true;
		name=temp;
	}while(number || t2);
	stringstream ss;
	ss << "SELECT lname, fname, ssn FROM company.EMPLOYEE WHERE Lname='" << name << "'";

	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)
		ss.str().c_str(), SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"Option A\"" << endl;
		exit(1);
	}

	SQLINTEGER name_len;
	SQLCHAR fname[16], lname[16];
	SQLINTEGER ssn;

	retvalue = SQLBindCol(hstmt1, 1, SQL_C_CHAR, &lname, sizeof(lname), &name_len);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 1" << endl;
		exit(1);
	}

	retvalue = SQLBindCol(hstmt1, 2, SQL_C_CHAR, &fname, sizeof(fname), &name_len);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 2" << endl;
		exit(1);
	}

	retvalue = SQLBindCol(hstmt1, 3, SQL_C_LONG, &ssn, sizeof(ssn), &name_len);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 3" << endl;
		exit(1);
	}
	
	vector<int> ssns;
	int count=0;
	int sn;
	while((retvalue=SQLFetch(hstmt1))!= SQL_NO_DATA)
	{
		cout << endl;
		cout << lname << ",\t" << fname << "\t" << ssn << "\t" << endl;
		ssns.push_back(ssn);
		count++;
	}
	if(count==0)
	{
		cout << "\nI'm sorry, but there are no records with the requested last name.\n\n";
		return;
	}else if(count>1)
	{
		cout << "\nSelect an ssn from the list. ";
		
		bool good;
		redo=false;
		do
		{
			if(redo)
				cout << "\nI'm sorry, but that was not a valid ssn. Please try again.\nSelect an ssn from the list. ";
			cin >> sn;
			cin.clear();
			good=false;
			for(int i=0; i<count; ++i)
			{
				if(sn==ssns[i])
					good=true;
			}
			redo=true;
		}while(!good);
	}
	cout << "\n\nFirst Name\tLast Name\t SSN\n";
	traverse(sn, retvalue, hdbc1, hstmt1);

}

void option_b(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1)
{
	cout << "\n\n\t\tThe 5 workers with the highest salaries are:\n";

	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)
		"SELECT TOP 5 ssn, fname, lname, salary FROM company.employee ORDER BY salary DESC", SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"Option B\"" << endl;
		exit(1);
	}

	SQLINTEGER name_len;
	SQLCHAR ssn[10], fname[16], lname[16], salary[11];

	retvalue = SQLBindCol(hstmt1, 1, SQL_C_CHAR, &ssn, sizeof(ssn), &name_len);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 1" << endl;
		exit(1);
	}

	retvalue = SQLBindCol(hstmt1, 2, SQL_C_CHAR, &fname, sizeof(fname), &name_len);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 2" << endl;
		exit(1);
	}

	retvalue = SQLBindCol(hstmt1, 3, SQL_C_CHAR, &lname, sizeof(lname), &name_len);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 3" << endl;
		exit(1);
	}

	retvalue = SQLBindCol(hstmt1, 4, SQL_C_CHAR, &salary, sizeof(salary), &name_len);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 4" << endl;
		exit(1);
	}
	cout << "\nSSN\t\tFirst Name\tLast Name\tSalary\n";
	while((retvalue=SQLFetch(hstmt1))!= SQL_NO_DATA)
	{
		cout << endl;
		cout << ssn << "\t" << fname << "\t\t" << lname << "\t\t" << salary << endl;
	}


}

void option_c(SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1)
{
	cout << "\n\n\t\tThe five workers with the most hours are:\n";

	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)
		"SELECT TOP 5 e.ssn, e.fname, e.lname, w.hours FROM company.EMPLOYEE e JOIN company.WORKS_ON w ON e.ssn=w.essn ORDER BY w.Hours DESC", SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"Option B\"" << endl;
		exit(1);
	}

	SQLINTEGER name_len;
	SQLCHAR ssn[10], fname[16], lname[16], hours[4];

	retvalue = SQLBindCol(hstmt1, 1, SQL_C_CHAR, &ssn, sizeof(ssn), &name_len);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 1" << endl;
		exit(1);
	}

	retvalue = SQLBindCol(hstmt1, 2, SQL_C_CHAR, &fname, sizeof(fname), &name_len);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 2" << endl;
		exit(1);
	}

	retvalue = SQLBindCol(hstmt1, 3, SQL_C_CHAR, &lname, sizeof(lname), &name_len);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 3" << endl;
		exit(1);
	}

	retvalue = SQLBindCol(hstmt1, 4, SQL_C_CHAR, &hours, sizeof(hours), &name_len);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 4" << endl;
		exit(1);
	}

	cout << "\nSSN\t\tFirst Name\tLast Name\tHours\n";
	while((retvalue=SQLFetch(hstmt1))!= SQL_NO_DATA)
	{
		cout << endl;
		cout << ssn << "\t" << fname << "\t\t" << lname << "\t\t" << hours << ".0" << endl;
	}


}

void traverse(int sn, SQLRETURN& retvalue, SQLHDBC& hdbc1, SQLHSTMT& hstmt1)
{
	vector<int> ssns;
	stringstream ss;
	SQLINTEGER name_len;
	SQLCHAR fname[16], lname[16];
	SQLINTEGER ssn;
	int count=0;

	deallocate_handle(retvalue, hdbc1, hstmt1);
	allocate_handle(retvalue, hdbc1, hstmt1);

	ss << "SELECT fname, lname, ssn FROM company.EMPLOYEE WHERE Super_ssn=" << sn;
	retvalue = SQLExecDirect(hstmt1, (SQLCHAR*)
		ss.str().c_str(), SQL_NTS);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to execute \"Option A\"" << endl;
		exit(1);
	}

	retvalue = SQLBindCol(hstmt1, 1, SQL_C_CHAR, &fname, sizeof(fname), &name_len);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 1" << endl;
		exit(1);
	}

	retvalue = SQLBindCol(hstmt1, 2, SQL_C_CHAR, &lname, sizeof(lname), &name_len);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 2" << endl;
		exit(1);
	}

	retvalue = SQLBindCol(hstmt1, 3, SQL_C_LONG, &ssn, sizeof(ssn), &name_len);
	if(retvalue != SQL_SUCCESS && retvalue != SQL_SUCCESS_WITH_INFO)
	{
		cerr << "Failed to bind column 3" << endl;
		exit(1);
	}

	while((retvalue=SQLFetch(hstmt1))!= SQL_NO_DATA)
	{
		cout << endl;
		cout << fname << "\t\t" << lname << "\t\t" << ssn << endl;
		++count;
		ssns.push_back(ssn);
	}
	for(int i=0; i<count; ++i)
	{
		traverse(ssns[i], retvalue, hdbc1, hstmt1);
	}

}